﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Reflection;
using System.Configuration;
using Analysis.Business;
using System.Windows.Forms;


namespace Analysis.SimpleLayer
{
    public class Factory
    {
        static Factory nesne;

        string cnnString = Config.GetConfig.GetValue(Parametre.ConStr);
        public string CnnString
        {
            get { return cnnString; }
            set { cnnString = value; }
        }

        private Hashtable paramCache = Hashtable.Synchronized(new Hashtable());

        #region Parametre
        public string GetParameterToken()
        {
            return "@";
        }
        #region Generic
        public SqlParameter GetParameter<T>(string paramName, SqlDbType tip, T value)
        {
            SqlParameter prm = new SqlParameter(paramName, tip);
            prm.Value = value;
            return prm;
        }
        public SqlParameter GetParameter<T>(string paramName, SqlDbType tip, T value, Int32 size)
        {
            SqlParameter prm = new SqlParameter(paramName, tip, size);
            prm.Value = value;
            return prm;
        }
        #endregion
        #region Collection
        public SqlParameter GetParameter(string paramName, SqlDbType tip, object value, Int32 size)
        {
            SqlParameter prm = new SqlParameter(paramName, tip, size);
            prm.Value = value;
            return prm;
        }
        public SqlParameter GetParameter(string paramName, SqlDbType tip, object value)
        {
            SqlParameter prm = new SqlParameter(paramName, tip);
            prm.Value = value;
            return prm;
        }
        public SqlParameter GetParameter(string paramName, SqlDbType tip)
        {
            return new SqlParameter(paramName, tip);
        }
        #endregion
        #endregion

        #region Singleton Ctor
        private Factory()
        {

        }
        public static Factory GetFactory
        {
            get
            {
                if (nesne == null)
                {
                    nesne = new Factory();
                }
                return nesne;
            }
        }
        #endregion

        #region Caching
        public SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
        {
            SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];

            for (int i = 0, j = originalParameters.Length; i < j; i++)
            {
                clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();
            }

            return clonedParameters;
        }
        bool IsParameterCached(string procName)
        {
            string key = CnnString + ":" + procName;
            return paramCache.ContainsKey(key);
        }
        void SetParameterCached(SqlCommand cmd)
        {
            string key = CnnString + ":" + cmd.CommandText;
            List<SqlParameter> lst = new List<SqlParameter>();
            foreach (SqlParameter item in cmd.Parameters)
            {
                lst.Add(item);
            }
            paramCache.Add(key, lst.ToArray());
        }
        void GetCachedParameterSet(SqlCommand cmd)
        {
            string key = CnnString + ":" + cmd.CommandText;
            SqlParameter[] param = null;
            if (paramCache.ContainsKey(key) == true)
            {
                param = (SqlParameter[])paramCache[key];
            }

            if (param != null)
            {
                SqlParameter[] prm = CloneParameters(param);
                foreach (SqlParameter item in prm)
                {
                    cmd.Parameters.Add(item);
                }
            }
        }
        #endregion

        #region TransActions
        public SqlTransaction OpenSession()
        {
            SqlConnection cn = new SqlConnection(CnnString);
            cn.Open();
            SqlTransaction trn = cn.BeginTransaction();
            return trn;
        }
        #endregion

        #region ExecuteNonQuery
        public int ExecuteNonQuery(string commandText)
        {
            int sonuc = -1;
            using (SqlConnection cn = new SqlConnection(CnnString))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand command = new SqlCommand(commandText))
                    {
                        command.Connection = cn;
                        command.CommandTimeout = 999999;
                        command.CommandType = CommandType.Text;
                        sonuc = command.ExecuteNonQuery();
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(String.Format("Hata Oluştu ! \r\n {0}", ex.Message));
                }
                finally
                {
                    if (cn.State == ConnectionState.Open) cn.Close();
                }
            }
            return sonuc;
        }
        public int ExecuteNonQuery(CommandType commandType, string commandText)
        {
            int sonuc = -1;
            using (SqlConnection cn = new SqlConnection(CnnString))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand command = new SqlCommand(commandText, cn))
                    {
                        command.CommandType = commandType;
                        command.CommandTimeout = 999999;
                        sonuc = command.ExecuteNonQuery();
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(String.Format("Hata Oluştu ! \r\n {0}", ex.Message));
                }
                finally
                {
                    if (cn.State == ConnectionState.Open) cn.Close();
                }

            }
            return sonuc;
        }
        public int ExecuteNonQuery(string commandText, SqlTransaction trn)
        {
            int sonuc = -1;
            using (SqlCommand command = new SqlCommand(commandText))
            {
                command.Connection = trn.Connection;
                command.CommandTimeout = 999999;
                command.Transaction = trn;
                sonuc = command.ExecuteNonQuery();
            }
            return sonuc;
        }
        public int ExecuteNonQuery(CommandType commandType, string commandText, SqlTransaction trn)
        {
            int sonuc = -1;
            using (SqlCommand command = new SqlCommand(commandText))
            {
                command.CommandType = commandType;
                command.CommandTimeout = 999999;
                command.Connection = trn.Connection;
                command.Transaction = trn;
                sonuc = command.ExecuteNonQuery();
            }
            return sonuc;
        }
        public int ExecuteNonQuery(CommandType commandType, string ProcName, params object[] value)
        {
            int sonuc = -1;
            using (SqlConnection cn = new SqlConnection(CnnString))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand command = new SqlCommand(ProcName))
                    {
                        command.Connection = cn;
                        command.CommandTimeout = 999999;
                        command.CommandType = commandType;
                        if (IsParameterCached(ProcName))
                        {
                            GetCachedParameterSet(command);
                        }
                        else
                        {
                            SqlCommandBuilder.DeriveParameters(command);
                            SetParameterCached(command);
                        }
                        for (int i = 0; i < value.Length; i++)
                        {
                            SqlParameter prm = (SqlParameter)command.Parameters[i + 1];
                            prm.Value = value[i];
                        }
                        sonuc = command.ExecuteNonQuery();
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(String.Format("Hata Oluştu ! \r\n {0}", ex.Message));
                }
                finally
                {
                    if (cn.State == ConnectionState.Open) cn.Close();
                }

            }
            return sonuc;
        }
        public int ExecuteNonQuery(CommandType commandType, string ProcName, SqlTransaction trn, params object[] value)
        {
            int sonuc = -1;
            using (SqlCommand command = new SqlCommand(ProcName))
            {
                command.CommandTimeout = 999999;
                command.Connection = trn.Connection;
                command.Transaction = trn;
                command.CommandType = commandType;
                if (IsParameterCached(ProcName))
                {
                    GetCachedParameterSet(command);
                }
                else
                {
                    SqlCommandBuilder.DeriveParameters(command);
                    SetParameterCached(command);
                }
                for (int i = 0; i < value.Length; i++)
                {
                    SqlParameter prm = (SqlParameter)command.Parameters[i + 1];
                    prm.Value = value[i];
                }
                sonuc = command.ExecuteNonQuery();
            }
            return sonuc;
        }
        public int ExecuteNonQuery(CommandType commandType, string commandText, params SqlParameter[] value)
        {
            int sonuc = -1;

            using (SqlConnection cn = new SqlConnection(CnnString))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand command = new SqlCommand(commandText))
                    {
                        command.CommandTimeout = 999999;
                        command.Connection = cn;
                        command.CommandType = commandType;
                        foreach (SqlParameter item in value)
                        {
                            command.Parameters.Add(item);
                        }
                        sonuc = command.ExecuteNonQuery();
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(String.Format("Hata Oluştu ! \r\n {0}", ex.Message));
                }
                finally
                {
                    if (cn.State == ConnectionState.Open) cn.Close();
                }

            }
            return sonuc;
        }
        public int ExecuteNonQuery(CommandType commandType, string commandText, SqlTransaction trn, params SqlParameter[] value)
        {
            int sonuc = -1;
            using (SqlCommand command = new SqlCommand(commandText))
            {
                command.CommandTimeout = 999999;
                command.Connection = trn.Connection;
                command.Transaction = trn;
                command.CommandType = commandType;
                foreach (SqlParameter item in value)
                {
                    command.Parameters.Add(item);
                }
                sonuc = command.ExecuteNonQuery();
            }

            return sonuc;
        }
        #endregion

        //#region Execute Readers
        //public SqlDataReader ExecuteReader(string commandText)
        //{
        //    SqlDataReader rd = null;
        //    SqlConnection cn = new SqlConnection(CnnString);
        //    try
        //    {
        //        cn.Open();
        //        using (SqlCommand cmd = new SqlCommand(commandText))
        //        {
        //            cmd.Connection = cn;
        //            cmd.CommandTimeout = 999999;
        //            cmd.CommandType = CommandType.Text;
        //            cmd.CommandTimeout = 999999;
        //            rd = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        if (cn.State == ConnectionState.Open) cn.Close();
        //        MessageBox.Show(String.Format("Hata Oluştu ! \r\n {0}", ex.Message));
        //    }
        //    finally
        //    {

        //    }
        //    return rd;
        //}
        //public SqlDataReader ExecuteReader(string commandText, SqlTransaction trn)
        //{
        //    SqlDataReader rd = null;
        //    using (SqlCommand cmd = new SqlCommand())
        //    {
        //        cmd.Connection = trn.Connection;
        //        cmd.Transaction = trn;
        //        cmd.CommandType = CommandType.Text;
        //        cmd.CommandTimeout = 999999;
        //        cmd.CommandText = commandText;
        //        rd = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        //    }
        //    return rd;
        //}
        //public SqlDataReader ExecuteReader(CommandType commandType, string commandText)
        //{
        //    SqlDataReader rd = null;
        //    SqlConnection cn = new SqlConnection(CnnString);
        //    try
        //    {
        //        cn.Open();
        //        using (SqlCommand cmd = new SqlCommand())
        //        {
        //            cmd.Connection = cn;
        //            cmd.CommandTimeout = 999999;
        //            cmd.CommandType = commandType;
        //            cmd.CommandText = commandText;
        //            rd = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        if (cn.State == ConnectionState.Open) cn.Close();
        //        MessageBox.Show(String.Format("Hata Oluştu ! \r\n {0}", ex.Message));
        //    }
        //    finally
        //    {

        //    }
        //    return rd;
        //}
        //public SqlDataReader ExecuteReader(CommandType commandType, string commandText, SqlTransaction trn)
        //{
        //    SqlDataReader rd = null;
        //    using (SqlCommand cmd = new SqlCommand())
        //    {
        //        cmd.Connection = trn.Connection;
        //        cmd.Transaction = trn;
        //        cmd.CommandTimeout = 999999;
        //        cmd.CommandType = commandType;
        //        cmd.CommandText = commandText;
        //        rd = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        //    }
        //    return rd;
        //}
        //public SqlDataReader ExecuteReader(string ProcName, params object[] value)
        //{
        //    SqlDataReader rd = null;
        //    SqlConnection cn = new SqlConnection(CnnString);
        //    try
        //    {
        //        cn.Open();
        //        using (SqlCommand command = new SqlCommand(ProcName))
        //        {
        //            command.Connection = cn;
        //            command.CommandTimeout = 999999;
        //            command.CommandType = CommandType.StoredProcedure;
        //            if (IsParameterCached(ProcName))
        //            {
        //                GetCachedParameterSet(command);
        //            }
        //            else
        //            {
        //                SqlCommandBuilder.DeriveParameters(command);
        //                SetParameterCached(command);
        //            }
        //            for (int i = 0; i < value.Length; i++)
        //            {
        //                SqlParameter prm = (SqlParameter)command.Parameters[i + 1];
        //                prm.Value = value[i];
        //            }
        //            rd = command.ExecuteReader(CommandBehavior.CloseConnection);
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        if (cn.State == ConnectionState.Open) cn.Close();
        //        MessageBox.Show(String.Format("Hata Oluştu ! \r\n {0}", ex.Message));
        //    }
        //    finally
        //    {
        //    }
        //    return rd;
        //}
        //public SqlDataReader ExecuteReader(string ProcName, SqlTransaction trn, params object[] value)
        //{
        //    SqlDataReader rd = null;
        //    using (SqlCommand command = new SqlCommand(ProcName))
        //    {
        //        command.Connection = trn.Connection;
        //        command.Transaction = trn;
        //        command.CommandTimeout = 999999;
        //        command.CommandType = CommandType.StoredProcedure;
        //        if (IsParameterCached(ProcName))
        //        {
        //            GetCachedParameterSet(command);
        //        }
        //        else
        //        {
        //            SqlCommandBuilder.DeriveParameters(command);
        //            SetParameterCached(command);
        //        }
        //        for (int i = 0; i < value.Length; i++)
        //        {
        //            SqlParameter prm = (SqlParameter)command.Parameters[i + 1];
        //            prm.Value = value[i];
        //        }
        //        rd = command.ExecuteReader(CommandBehavior.CloseConnection);
        //    }
        //    return rd;
        //}
        //public SqlDataReader ExecuteReader(CommandType commandType, string commandText, params SqlParameter[] value)
        //{
        //    SqlDataReader rd = null;
        //    SqlConnection cn = new SqlConnection(CnnString);
        //    try
        //    {
        //        cn.Open();
        //        using (SqlCommand command = new SqlCommand(commandText))
        //        {
        //            command.Connection = cn;
        //            command.CommandTimeout = 999999;
        //            command.CommandType = commandType;
        //            foreach (SqlParameter item in value)
        //            {
        //                command.Parameters.Add(item);
        //            }
        //            rd = command.ExecuteReader(CommandBehavior.CloseConnection);
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        if (cn.State == ConnectionState.Open) cn.Close();
        //        MessageBox.Show(String.Format("Hata Oluştu ! \r\n {0}", ex.Message));
        //    }
        //    finally
        //    {
        //    }
        //    return rd;
        //}
        //public SqlDataReader ExecuteReader(CommandType commandType, string commandText, SqlTransaction trn, params SqlParameter[] value)
        //{
        //    SqlDataReader rd = null;
        //    using (SqlCommand command = new SqlCommand(commandText))
        //    {
        //        command.Connection = trn.Connection;
        //        command.Transaction = trn;
        //        command.CommandTimeout = 999999;
        //        command.CommandType = commandType;
        //        foreach (SqlParameter item in value)
        //        {
        //            command.Parameters.Add(item);
        //        }
        //        rd = command.ExecuteReader(CommandBehavior.CloseConnection);
        //    }
        //    return rd;
        //}
        //#endregion

        #region Execute Scaler
        public object ExecuteScaler(string commandText)
        {
            object sc = null;
            using (SqlConnection cn = new SqlConnection(CnnString))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(commandText))
                    {
                        cmd.Connection = cn;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandTimeout = 999999;
                        sc = cmd.ExecuteScalar();
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(String.Format("Hata Oluştu ! \r\n {0}", ex.Message));
                }
                finally
                {
                    if (cn.State == ConnectionState.Open) cn.Close();
                }
            }
            return sc;
        }
        public object ExecuteScaler(CommandType commandType, string commandText)
        {
            object sc = null;
            using (SqlConnection cn = new SqlConnection(CnnString))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.Connection = cn;
                        cmd.CommandType = commandType;
                        cmd.CommandText = commandText;
                        cmd.CommandTimeout = 999999;
                        sc = cmd.ExecuteScalar();
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(String.Format("Hata Oluştu ! \r\n {0}", ex.Message));
                }
                finally
                {
                    if (cn.State == ConnectionState.Open) cn.Close();
                }
            }
            return sc;
        }
        public object ExecuteScaler(string commandText, SqlTransaction trn)
        {
            object sc = null;
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = trn.Connection;
                cmd.Transaction = trn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = 999999;
                cmd.CommandText = commandText;
                sc = cmd.ExecuteScalar();
            }
            return sc;
        }
        public object ExecuteScaler(CommandType commandType, string commandText, SqlTransaction trn)
        {
            object sc = null;
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = trn.Connection;
                cmd.Transaction = trn;
                cmd.CommandTimeout = 999999;
                cmd.CommandType = commandType;
                cmd.CommandText = commandText;
                sc = cmd.ExecuteScalar();
            }
            return sc;
        }
        public object ExecuteScaler(CommandType commandType, string ProcName, params object[] value)
        {
            object sc = null;
            using (SqlConnection cn = new SqlConnection(CnnString))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand command = new SqlCommand(ProcName))
                    {
                        command.Connection = cn;
                        command.CommandTimeout = 999999;
                        command.CommandType = commandType;
                        if (IsParameterCached(ProcName))
                        {
                            GetCachedParameterSet(command);
                        }
                        else
                        {
                            SqlCommandBuilder.DeriveParameters(command);
                            SetParameterCached(command);
                        }
                        for (int i = 0; i < value.Length; i++)
                        {
                            SqlParameter prm = (SqlParameter)command.Parameters[i + 1];
                            prm.Value = value[i];
                        }
                        sc = command.ExecuteScalar();
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(String.Format("Hata Oluştu ! \r\n {0}", ex.Message));
                }
                finally
                {
                    if (cn.State == ConnectionState.Open) cn.Close();
                }
            }
            return sc;
        }
        public object ExecuteScaler(CommandType commandType, string ProcName, SqlTransaction trn, params object[] value)
        {
            object sc = null;
            using (SqlCommand command = new SqlCommand(ProcName))
            {
                command.Connection = trn.Connection;
                command.Transaction = trn;
                command.CommandTimeout = 999999;
                command.CommandType = commandType;
                if (IsParameterCached(ProcName))
                {
                    GetCachedParameterSet(command);
                }
                else
                {
                    SqlCommandBuilder.DeriveParameters(command);
                    SetParameterCached(command);
                }
                for (int i = 0; i < value.Length; i++)
                {
                    SqlParameter prm = (SqlParameter)command.Parameters[i + 1];
                    prm.Value = value[i];
                }
                sc = command.ExecuteScalar();
            }
            return sc;
        }
        public object ExecuteScaler(CommandType commandType, string commandText, params SqlParameter[] value)
        {
            object sc = null;
            using (SqlConnection cn = new SqlConnection(CnnString))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand command = new SqlCommand(commandText))
                    {
                        command.Connection = cn;
                        command.CommandTimeout = 999999;
                        command.CommandType = commandType;
                        foreach (SqlParameter item in value)
                        {
                            command.Parameters.Add(item);
                        }
                        sc = command.ExecuteScalar();
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(String.Format("Hata Oluştu ! \r\n {0}", ex.Message));
                }
                finally
                {
                    if (cn.State == ConnectionState.Open) cn.Close();
                }
            }
            return sc;
        }
        public object ExecuteScaler(CommandType commandType, string commandText, SqlTransaction trn, params SqlParameter[] value)
        {
            object sc = null;
            using (SqlCommand command = new SqlCommand(commandText))
            {
                command.Connection = trn.Connection;
                command.CommandTimeout = 999999;
                command.Transaction = trn;
                command.CommandType = commandType;
                foreach (SqlParameter item in value)
                {
                    command.Parameters.Add(item);
                }
                sc = command.ExecuteScalar();
            }
            return sc;
        }
        #endregion

        #region Execute DataTable
        public DataTable ExecuteDataTable(string CommandText)
        {
            DataSet ds = new DataSet();
            using (SqlConnection cn = new SqlConnection(CnnString))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.Connection = cn;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandTimeout = 999999;
                        cmd.CommandText = CommandText;
                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {
                            da.Fill(ds);
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(String.Format("Hata Oluştu ! \r\n {0}", ex.Message));
                }
                finally
                {
                    if (cn.State == ConnectionState.Open) cn.Close();
                }
            }
            return ds.Tables[0];
        }
        public DataTable ExecuteDataTable(CommandType cmdtype, string CommandText)
        {
            DataSet dt = new DataSet();
            using (SqlConnection cn = new SqlConnection(CnnString))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(CommandText))
                    {
                        cmd.Connection = cn;
                        cmd.CommandTimeout = 999999;
                        cmd.CommandType = cmdtype;
                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {
                            da.Fill(dt);
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(String.Format("Hata Oluştu ! \r\n {0}", ex.Message));
                }
                finally
                {
                    if (cn.State == ConnectionState.Open) cn.Close();
                }
            }
            return dt.Tables[0];
        }
        public DataTable ExecuteDataTable(string commandText, SqlTransaction trn)
        {
            DataSet ds = new DataSet();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = trn.Connection;
                cmd.Transaction = trn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = 999999;
                cmd.CommandText = commandText;
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    da.Fill(ds);
                }
            }
            return ds.Tables[0];
        }
        public DataTable ExecuteDataTable(CommandType commandType, string commandText, SqlTransaction trn)
        {
            DataSet ds = new DataSet();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = trn.Connection;
                cmd.CommandTimeout = 999999;
                cmd.Transaction = trn;
                cmd.CommandType = commandType;
                cmd.CommandText = commandText;
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    da.Fill(ds);
                }
            }
            return ds.Tables[0];
        }

        public DataTable ExecuteDataTable(CommandType commandType, string ProcName, params object[] value)
        {
            DataSet ds = new DataSet();
            using (SqlConnection cn = new SqlConnection(CnnString))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand command = new SqlCommand(ProcName))
                    {
                        command.Connection = cn;
                        command.CommandTimeout = 999999;
                        command.CommandType = commandType;
                        if (IsParameterCached(ProcName))
                        {
                            GetCachedParameterSet(command);
                        }
                        else
                        {
                            SqlCommandBuilder.DeriveParameters(command);
                            SetParameterCached(command);
                        }
                        for (int i = 0; i < value.Length; i++)
                        {
                            SqlParameter prm = (SqlParameter)command.Parameters[i + 1];
                            prm.Value = value[i];
                        }
                        using (SqlDataAdapter da = new SqlDataAdapter(command))
                        {
                            da.Fill(ds);
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(String.Format("Hata Oluştu ! \r\n {0}", ex.Message));
                }
                finally
                {
                    if (cn.State == ConnectionState.Open) cn.Close();
                }
            }
            return ds.Tables[0];
        }
        public DataTable ExecuteDataTable(CommandType commandType, string ProcName, SqlTransaction trn, params object[] value)
        {
            DataSet ds = new DataSet();
            using (SqlCommand command = new SqlCommand(ProcName))
            {
                command.Connection = trn.Connection;
                command.CommandTimeout = 999999;
                command.Transaction = trn;
                command.CommandType = commandType;
                if (IsParameterCached(ProcName))
                {
                    GetCachedParameterSet(command);
                }
                else
                {
                    SqlCommandBuilder.DeriveParameters(command);
                    SetParameterCached(command);
                }
                for (int i = 0; i < value.Length; i++)
                {
                    SqlParameter prm = (SqlParameter)command.Parameters[i + 1];
                    prm.Value = value[i];
                }
                using (SqlDataAdapter da = new SqlDataAdapter(command))
                {
                    da.Fill(ds);
                }
            }
            return ds.Tables[0];

        }
        public DataTable ExecuteDataTable(CommandType commandType, string commandText, params SqlParameter[] value)
        {
            DataSet ds = new DataSet();
            using (SqlConnection cn = new SqlConnection(CnnString))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand command = new SqlCommand(commandText))
                    {
                        command.Connection = cn;
                        command.CommandTimeout = 999999;
                        command.CommandType = commandType;
                        foreach (SqlParameter item in value)
                        {
                            command.Parameters.Add(item);
                        }
                        using (SqlDataAdapter da = new SqlDataAdapter(command))
                        {
                            da.Fill(ds);
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(String.Format("Hata Oluştu ! \r\n {0}", ex.Message));
                }
                finally
                {
                    if (cn.State == ConnectionState.Open) cn.Close();
                }
            }
            return ds.Tables[0];
        }
        public DataTable ExecuteDataTable(CommandType commandType, string commandText, SqlTransaction trn, params SqlParameter[] value)
        {
            DataSet ds = new DataSet();
            using (SqlCommand command = new SqlCommand(commandText))
            {
                command.Connection = trn.Connection;
                command.CommandTimeout = 999999;
                command.Transaction = trn;
                command.CommandType = commandType;
                foreach (SqlParameter item in value)
                {
                    command.Parameters.Add(item);
                }
                using (SqlDataAdapter da = new SqlDataAdapter(command))
                {
                    da.Fill(ds);
                }
            }
            return ds.Tables[0];
        }
        #endregion

        //#region Execute DataSet
        //public DataSet ExecuteDataSet(string CommandText)
        //{
        //    DataSet ds = new DataSet();
        //    using (SqlConnection cn = new SqlConnection(CnnString))
        //    {
        //        try
        //        {
        //            cn.Open();
        //            using (SqlCommand cmd = new SqlCommand())
        //            {
        //                cmd.Connection = cn;
        //                cmd.CommandType = CommandType.Text;
        //                cmd.CommandTimeout = 999999;
        //                cmd.CommandText = CommandText;
        //                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        //                {
        //                    da.Fill(ds);
        //                }
        //            }
        //        }
        //        catch (Exception ex)
        //        {
        //            MessageBox.Show(String.Format("Hata Oluştu ! \r\n {0}", ex.Message));
        //        }
        //        finally
        //        {
        //            if (cn.State == ConnectionState.Open) cn.Close();
        //        }
        //    }
        //    return ds;
        //}
        //public DataSet ExecuteDataSet(CommandType cmdtype, string CommandText)
        //{
        //    DataSet dt = new DataSet();
        //    using (SqlConnection cn = new SqlConnection(CnnString))
        //    {
        //        try
        //        {
        //            cn.Open();
        //            using (SqlCommand cmd = new SqlCommand(CommandText))
        //            {
        //                cmd.Connection = cn;
        //                cmd.CommandType = cmdtype;
        //                cmd.CommandTimeout = 99999;
        //                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        //                {
        //                    da.Fill(dt);
        //                }
        //            }
        //        }
        //        catch (Exception ex)
        //        {
        //            MessageBox.Show(String.Format("Hata Oluştu ! \r\n {0}", ex.Message));
        //        }
        //        finally
        //        {
        //            if (cn.State == ConnectionState.Open) cn.Close();
        //        }
        //    }
        //    return dt;
        //}
        //public DataSet ExecuteDataSet(string commandText, SqlTransaction trn)
        //{
        //    DataSet ds = new DataSet();
        //    using (SqlCommand cmd = new SqlCommand())
        //    {
        //        cmd.Connection = trn.Connection;
        //        cmd.Transaction = trn;
        //        cmd.CommandType = CommandType.Text;
        //        cmd.CommandTimeout = 999999;
        //        cmd.CommandText = commandText;
        //        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        //        {
        //            da.Fill(ds);
        //        }
        //    }
        //    return ds;
        //}
        //public DataSet ExecuteDataSet(CommandType commandType, string commandText, SqlTransaction trn)
        //{
        //    DataSet ds = new DataSet();
        //    using (SqlCommand cmd = new SqlCommand())
        //    {
        //        cmd.Connection = trn.Connection;
        //        cmd.Transaction = trn;
        //        cmd.CommandTimeout = 999999;
        //        cmd.CommandType = commandType;
        //        cmd.CommandText = commandText;
        //        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        //        {
        //            da.Fill(ds);
        //        }
        //    }
        //    return ds;
        //}
        //public DataSet ExecuteDataSet(CommandType commandType, string ProcName, params object[] value)
        //{
        //    DataSet ds = new DataSet();
        //    using (SqlConnection cn = new SqlConnection(CnnString))
        //    {
        //        try
        //        {
        //            cn.Open();
        //            using (SqlCommand command = new SqlCommand(ProcName))
        //            {
        //                command.Connection = cn;
        //                command.CommandTimeout = 999999;
        //                command.CommandType = commandType;
        //                if (IsParameterCached(ProcName))
        //                {
        //                    GetCachedParameterSet(command);
        //                }
        //                else
        //                {
        //                    SqlCommandBuilder.DeriveParameters(command);
        //                    SetParameterCached(command);
        //                }
        //                for (int i = 0; i < value.Length; i++)
        //                {
        //                    SqlParameter prm = (SqlParameter)command.Parameters[i + 1];
        //                    prm.Value = value[i];
        //                }
        //                using (SqlDataAdapter da = new SqlDataAdapter(command))
        //                {
        //                    da.Fill(ds);
        //                }
        //            }
        //        }
        //        catch (Exception ex)
        //        {
        //            MessageBox.Show(String.Format("Hata Oluştu ! \r\n {0}", ex.Message));
        //        }
        //        finally
        //        {
        //            if (cn.State == ConnectionState.Open) cn.Close();
        //        }
        //    }
        //    return ds;
        //}
        //public DataSet ExecuteDataSet(CommandType commandType, string ProcName, SqlTransaction trn, params object[] value)
        //{
        //    DataSet ds = new DataSet();
        //    using (SqlCommand command = new SqlCommand(ProcName))
        //    {
        //        command.Connection = trn.Connection;
        //        command.CommandTimeout = 999999;
        //        command.CommandType = commandType;
        //        command.Transaction = trn;
        //        if (IsParameterCached(ProcName))
        //        {
        //            GetCachedParameterSet(command);
        //        }
        //        else
        //        {
        //            SqlCommandBuilder.DeriveParameters(command);
        //            SetParameterCached(command);
        //        }
        //        for (int i = 0; i < value.Length; i++)
        //        {
        //            SqlParameter prm = (SqlParameter)command.Parameters[i + 1];
        //            prm.Value = value[i];
        //        }
        //        using (SqlDataAdapter da = new SqlDataAdapter(command))
        //        {
        //            da.Fill(ds);
        //        }
        //    }

        //    return ds;
        //}
        //public DataSet ExecuteDataSet(CommandType commandType, string commandText, params SqlParameter[] value)
        //{
        //    DataSet ds = new DataSet();
        //    using (SqlConnection cn = new SqlConnection(CnnString))
        //    {
        //        try
        //        {
        //            cn.Open();

        //            using (SqlCommand command = new SqlCommand(commandText))
        //            {
        //                command.Connection = cn;
        //                command.CommandTimeout = 999999;
        //                command.CommandType = commandType;
        //                foreach (SqlParameter item in value)
        //                {
        //                    command.Parameters.Add(item);
        //                }
        //                using (SqlDataAdapter da = new SqlDataAdapter(command))
        //                {
        //                    da.Fill(ds);
        //                }
        //            }
        //        }
        //        catch (Exception ex)
        //        {
        //            MessageBox.Show(String.Format("Hata Oluştu ! \r\n {0}", ex.Message));
        //        }
        //        finally
        //        {
        //            if (cn.State == ConnectionState.Open) cn.Close();
        //        }

        //    }
        //    return ds;
        //}
        //public DataSet ExecuteDataSet(CommandType commandType, string commandText, SqlTransaction trn, params SqlParameter[] value)
        //{
        //    DataSet ds = new DataSet();
        //    using (SqlCommand command = new SqlCommand(commandText))
        //    {
        //        command.Connection = trn.Connection;
        //        command.CommandTimeout = 999999;
        //        command.Transaction = trn;
        //        command.CommandType = commandType;
        //        foreach (SqlParameter item in value)
        //        {
        //            command.Parameters.Add(item);
        //        }
        //        using (SqlDataAdapter da = new SqlDataAdapter(command))
        //        {
        //            da.Fill(ds);
        //        }
        //    }
        //    return ds;
        //}
        //#endregion

    }
}
